#!/bin/sh -
# $Id: mysqlite3.sh 7557b5d4d82b 2012/12/02 09:07:16 pgurumur $
# Copyright (c) 2009 Prabhu Gurumurthy  <pgurumur@gmail.com>
# 
# Permission to use, copy, modify, and distribute this software for any
# purpose with or without fee is hereby granted, provided that the above
# copyright notice and this permission notice appear in all copies.
#
# THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
# WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
# MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
# ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
# WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
# ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
# OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
#

PROG=`basename $0`
MYSQLDUMP=`which mysqldump`
SQLITE=`which sqlite3`
SED=`which sed`
GREP=`which egrep`
PERL=`which perl`
CAT=`which cat`
RM=`which rm`

usage() {
   echo "${PROG}: options"
   echo "options: "
   echo " -d [ database name ]"
   echo " -u [ database user ]"
   echo " -h [ prints this help ]"
   exit 1
}

if [ $# -eq 0 ]; then
   usage
else
   sqlitedb=
   username=
   password=
   help=0
   seplist="\t"
   while getopts d:u:h option
   do
      case "$option" in
         d) sqlitedb="${OPTARG}" ;;
         u) username="${OPTARG}" ;;
         h) help=1 ;;
         *) usage ;;
      esac
   done

   if [ ${help} -eq 1 ]; then
      usage
   fi

   if [ ! -f ${SQLITE} ]; then
      echo "sqlite3 does not exist"
      exit 1
   fi

   if [ -z ${username} ]; then
      username=root
   fi

   echo -n "Enter MySQL password for ${username}: "
   stty -echo
   read password
   stty echo
   echo

   if [ ! -f ${sqlitedb} ]; then
      if [ -f ${MYSQLDUMP} -a -x ${MYSQLDUMP} ]; then
         ${MYSQLDUMP} -u ${username} -p ${password} --compact --compatible=ansi --default-character-set=binary ${sqlitedb} | 
         ${GREP} -v ' KEY "' |
         ${GREP} -v ' UNIQUE KEY "' |
         ${GREP} -v ' PRIMARY KEY ' | ${SED} 's/ unsigned / /g' |
         ${SED} 's/ auto_increment/ primary key autoincrement/gi' |
         ${SED} 's/ smallint([0-9]*) / integer /gi' |
         ${SED} 's/ tinyint([0-9]*) / integer /gi' |
         ${SED} 's/ int([0-9]*) / integer /gi' |
         ${SED} 's/ character set [^ ]* / /gi' |
         ${SED} 's/ enum([^)]*) / varchar(255) /gi' | 
         ${SED} 's/ on update [^,]*//gi' |
         ${PERL} -e 'local $/;$_=<>;s/,\n\)/\n\)/gs;print "begin;\n";print;print "commit;\n"' |
         ${PERL} -pe '
         if (/^(INSERT.+?)\(/) {
            $a=$1;
            s/\\'\''/'\'\''/g;
            s/\\n/\n/g;
            s/\),\(/\);\n$a\(/g; }' | ${GREP} -v 'SET' > ${sqlitedb}.sql

         ${CAT} ${sqlitedb}.sql | ${SQLITE} ${sqlitedb}.db > ${sqlitedb}.err
         ERRORS=`${CAT} ${sqliedb}.err | wc -l`
         if [ "$ERRORS" == "0" ]; then
            echo -n "Conversion completed without error. "
            echo "Output file: ${sqlitedb}.db"
            ${RM} ${sqlitedb}.sql
            ${RM} ${sqlitedb}.err
         else
            echo -n "There were errors during conversion. "
            echo "Review ${sqlitedb}.err and ${sqlitedb}.sql for details."
         fi
      fi
   else
      echo "${sqlitedb} already exists, will not overwrite"
      exit 1
   fi
fi
